package LogicSide;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DBside.JDBCConnection;
import DBside.TableItem;
import DBside.Tools;
import DBside.JDBCConnection.Table;

public class ManageSongs {

	/**
	 * 
	 * @param songName
	 * @param pgenre
	 *            - parent_genre_id
	 * @param genre
	 * @param artistName
	 * @param albumName
	 * @param s
	 *            - which represents the active session
	 * @return the song Id
	 * @throws SQLException
	 */
	public static int addSong(String songName, int pgenre, String genre,
			String artistName, String albumName, Session s) throws SQLException {
		JDBCConnection con = s.getCon();
		ResultSet rs = null, avsRS = null, sRS = null;
		int artistId = -1, albumId = -1, songId = -1, genreId = -1;
		try {
			con.getConnection().setAutoCommit(false);
			artistId = ArtistModifier.addNewArtist(artistName, s);
			if (artistId != -1) {
				// check if the specific song for this artist has been already
				// added
				TableItem[] t1 = { new TableItem("name", Tools.Type.VARCHAR,
						"songName") };
				sRS = con.select("id", t1, null, null, Table.SONG);
				if (sRS.next()) {
					TableItem[] t2 = {
							new TableItem("artist_id", Tools.Type.INT, artistId),
							new TableItem("song_id", Tools.Type.INT,
									sRS.getInt("id")) };
					avsRS = con.select("artist_id", t2, Tools.Connector.AND,
							null, Table.ARTISTVSSONG);
					if (!avsRS.next()) {
						genreId=addGenre(genre,pgenre,s);
						TableItem[] t3 = {
								new TableItem("name", Tools.Type.VARCHAR,
										songName),
								new TableItem("genre", Tools.Type.INT, genreId) };
						rs = con.insert(Table.SONG, t3);
						rs.next();
						songId = rs.getInt("id");
					}
				} else {
					genreId = addGenre(genre, pgenre, s);
					TableItem[] t3 = {
							new TableItem("name", Tools.Type.VARCHAR, songName),
							new TableItem("genre", Tools.Type.INT, genreId) };
					rs = con.insert(Table.SONG, t3);
					rs.next();
					songId = rs.getInt(1);
				}

				if (albumName != null) {
					albumId = AlbumModifier.addNewAlbum(albumName, s);
					if (albumId != -1) {
						TableItem[] t4 = {
								new TableItem("song_id", Tools.Type.INT, songId),
								new TableItem("album_id", Tools.Type.INT,
										albumId) };
						con.insert(Table.ALBUMVSSONG, t4);
					}
				}

			}
			con.getConnection().commit();
		} catch (SQLException e) {
			if (con.getConnection() != null)
				con.getConnection().rollback();
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (avsRS != null)
				avsRS.close();
			if (sRS != null)
				sRS.close();
		}
		return songId;
	}

	/**
	 * 
	 * @param songId
	 * @param songName
	 * @param s
	 *            - which represents the active session
	 * @return true if the operation succeed, else false
	 */
	public static boolean addSongToUser(int songId, String songName, Session s) {
		JDBCConnection con = s.getCon();
		int userId = s.getUser_id();
		TableItem[] t3 = { new TableItem("user_id", Tools.Type.INT, userId),
				new TableItem("song_id", Tools.Type.INT, songId) };
		con.insert(Table.USERVSSONG, t3);
		System.out.println("The song id is"+songId);
		java.util.Date today = new java.util.Date();
		HistoryModifier.addHistoryItem(s, songName, Tools.History.ADDEDSONG,
				new java.sql.Date(today.getTime()),songId);
		return true;

	}

	/**
	 * 
	 * @param genreName
	 * @param pgenreId
	 * @param s
	 *            -active session
	 * @return true if the operation succeed, else false
	 * @throws SQLException
	 */
	public static int addGenre(String genreName, int pgenreId, Session s) throws SQLException {
		JDBCConnection con = s.getCon();
		ResultSet rs = null;
		int genreId = -1;
		TableItem[] t3 = {
				new TableItem("name", Tools.Type.VARCHAR, genreName),
				new TableItem("parent_genre_id", Tools.Type.INT, pgenreId) };

		rs = con.select("id", t3, Tools.Connector.AND, null, Table.GENRE);
		if (!Tools.isEmpty(rs))
			genreId = rs.getInt("id");
		else {
			rs = con.insert(Table.GENRE, t3);
			rs.next();
			genreId = rs.getInt(1);
		}

		return genreId;

	}

	/**
	 * 
	 * @param songId
	 * @param s
	 *            - which represents the active session
	 * @return true if the operation succeed, else false
	 */
	public static boolean deleteSong(int songId, Session s) {

		boolean flag = true;
		int pid;
		TableItem[] t1 = { new TableItem("song_id", Tools.Type.INT, songId) };
		TableItem[] t2 = new TableItem[2];
		flag &= s.getCon().delete(t1, null, Table.USERVSSONG);
		List<String> lst = ManageSongPlaylist.getPlaylists(s);
		for (String plist : lst) {
			pid = ManageSongPlaylist.getPlaylistId(plist, s);
			t2[0] = new TableItem("songId", Tools.Type.INT, songId);
			t2[1] = new TableItem("playlistId", Tools.Type.INT, pid);
			flag &= s.getCon().delete(t2, Tools.Connector.AND,
					Table.PLAYLISTVSSONG);
		}
		return flag;
	}

	public static List<SongDetails> getAllUserSongs(Session s)
			throws SQLException {
		List<SongDetails> lst = new ArrayList<SongDetails>();

		ResultSet rs = null, srs = null;
		int user_id = s.getUser_id();
		try {
			s.getCon().getConnection().setAutoCommit(false);
			TableItem[] ti = { new TableItem("user_id", Tools.Type.INT, user_id) };
			rs = s.getCon()
					.select("song_id ", ti, null, null, Table.USERVSSONG);
			if (Tools.isEmpty(rs)) {
				s.getCon().getConnection().commit();
				return lst;
			}
			while (rs.next()) {

				lst.add(getSongDetails(s, rs.getInt("song_id")));
			}

			s.getCon().getConnection().commit();
		} catch (SQLException e) {
			if (s.getCon().getConnection() != null)
				s.getCon().getConnection().rollback();
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (srs != null)
				srs.close();
		}
		return lst;
	}

	public static SongDetails getSongDetails(Session s, int song_id)
			throws SQLException {
		SongDetails sd = new SongDetails();
		TableItem[] ti = new TableItem[1];
		int genre_id = -1, artist_id = -1, album_id = -1;
		ResultSet rs = null, grs = null;
		try {
			s.getCon().getConnection().setAutoCommit(false);
			sd.setSong_id(song_id);
			ti[0] = new TableItem("id", Tools.Type.INT, song_id);
			rs = s.getCon().select("name,genre ", ti, null, null, Table.SONG);
			if (!Tools.isEmpty(rs)) {
				rs.next();
				sd.setSong_name(rs.getString("name"));
				genre_id = rs.getInt("genre");
			}
			ti[0] = new TableItem("id", Tools.Type.INT, genre_id);
			grs = s.getCon().select("name ", ti, null, null, Table.GENRE);
			if (!Tools.isEmpty(grs)) {
				grs.next();
				sd.setGenre_name(grs.getString("name"));
			}
			ti[0] = new TableItem("song_id", Tools.Type.INT, song_id);
			rs = s.getCon().select("artist_id ", ti, null, null,
					Table.ARTISTVSSONG);
			if (!Tools.isEmpty(rs)) {
				rs.next();
				artist_id = rs.getInt("artist_id");
				ti[0] = new TableItem("id", Tools.Type.INT, artist_id);
				grs = s.getCon().select("name ", ti, null, null, Table.ARTIST);
				if (!Tools.isEmpty(grs)) {
					grs.next();
					sd.setArtist_name(grs.getString("name"));
				}
			}
			ti[0] = new TableItem("song_id", Tools.Type.INT, song_id);
			rs = s.getCon().select("album_id ", ti, null, null,
					Table.ALBUMVSSONG);
			if (!Tools.isEmpty(rs)) {
				rs.next();
				album_id = rs.getInt("album_id");
				ti[0] = new TableItem("id", Tools.Type.INT, album_id);
				grs = s.getCon().select("name,year ", ti, null, null, Table.ALBUM);
				if (!Tools.isEmpty(grs)) {
					grs.next();
					sd.setAlbum_name(grs.getString("name"));
					sd.setYear(grs.getInt("year"));
				}
			}
			s.getCon().getConnection().commit();
		} catch (SQLException e) {
			if (s.getCon().getConnection() != null)
				s.getCon().getConnection().rollback();
		} finally {
			if (rs != null)
				rs.close();
			if (grs != null)
				grs.close();
		}
		return sd;
	}
}
